TOP

Перестройка таблиц (Unpivot) в LibreOffice Calc

Описание

Иногда возникает необходимость превратить перекрестную (сводную) таблицу в плоскую. Данная процедура называется " распрямлением данных " (unpivot data) . В результате такого " распрямление мы получаем таблицу, где все подобные данные находятся в одной колонке.

Программа LibreOffice Calc не имеет стандартного функционала для преобразования сводных таблиц в " плоские Однако вы можете написать соответствующую процедуру самостоятельно. Давайте рассмотрим как это сделать.

Посмотрим на рисунок ниже, чтобы понять суть проблемы нагляднее. Слева у нас есть таблица в виде матрицы (сводная таблица), а справа – обычная таблица с колонками (плоская таблица):

Когда вы разворачиваете таблицу, вы распаковываете пару атрибут-значения, которая является точкой пересечения колонок в таблице-матрице и переориентируете их в распрямленные колонки в плоской таблице:

Значение (Values) (синее слева) разворачиваются в новую колонку (синее справа);

Атрибуты (Attributes) (зеленое слева) разворачиваются тоже в новую колонку (зеленое справа) и дублируются в соответствии с новой колонкой значений.

StarBASIC код для процедуры UnPivotTable

Откройте меню Tools - Macros - Edit Macros..., выберите Module1 и скопируйте следующий текст в модуль:

Sub UnPivotTable
  ' moonexcel.com.ua 
  Dim oBook        As Object
  Dim oActiveSheet As Object
  Dim oSelRange    As Object
  Dim oNewSheet    As Object
  
  Dim i                  As Long
  Dim iTopLabelRowCount  As Integer
  Dim iSideLabelColCount As Integer       
  
  oBook        = ThisComponent
  oActiveSheet = oBook.CurrentController.ActiveSheet
  oSelRange    = oBook.CurrentSelection
  
  ' Добавляем новый лист 
  oBook.Sheets.insertNewByName("UnPivoted Table",0)
  oNewSheet = oBook.Sheets(0)
  
  ' Определяем размер заголовков 
  iTopLabelRowCount  = InputBox(" Сколько строк в верхнем заголовке? ")
  iSideLabelColCount = InputBox(" Сколько колонок в боковом заголовке? ")
            
  i = 0
     
  For r = (iTopLabelRowCount + 1) To oSelRange.Rows.Count
    For c = (iSideLabelColCount + 1) To oSelRange.Columns.Count            
                       
      For j = 1 To iSideLabelColCount              
        oNewSheet.getCellByPosition(j-1, i).Formula = oSelRange.getCellByPosition(j-1, r-1).Formula               
      Next j
             
      For k = 1 To iTopLabelRowCount
        oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, k-1).Formula
      Next k           
                         
      oNewSheet.getCellByPosition(j + k - 2, i).Formula = oSelRange.getCellByPosition(c-1, r-1).Formula
      i = i + 1                   
            
    Next c
  Next r
End Sub

Затем закройте Macro Editor и вернитесь к вашему рабочему листу в LibreOffice Calc. Выберите всю таблицу с верхним и левым заголовками и запустите наш новый макрос через меню Tools - Macros - Run Macro...

Макрос вставит в вашу книгу новый лист с названием "UnPivoted Table" , а затем добавить таблицу в новом формате. С такой таблицей вы сможете произвести любой анализ в LibreOffice Calc.